# lottery_analysis.py
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from collections import Counter
import matplotlib
import re
from scipy.stats import f_oneway

# 设置中文字体防止乱码
matplotlib.rcParams['font.sans-serif'] = ['SimHei']
matplotlib.rcParams['axes.unicode_minus'] = False

# 读取数据
df = pd.read_excel("zhongjiang.xlsx")

# 转换日期格式并提取星期
df["开奖日期"] = pd.to_datetime(df["开奖日期"])
df["星期"] = df["开奖日期"].dt.day_name()

# 筛选目标开奖日（周一、三、六）
target_days = ["Monday", "Wednesday", "Saturday"]
filtered_df = df[df["星期"].isin(target_days)]

# 销售金额处理
filtered_df["总销售金额（元）"] = (
    filtered_df["总销售金额（元）"]
    .astype(str)
    .str.replace(",", "")
    .astype(float)
)


# 提取号码函数（按每个数字单独统计）
def extract_numbers(series, max_num):
    """提取单个数字并统计频率"""
    all_numbers = [f"{i:02d}" for i in range(1, max_num + 1)]
    counter = Counter()

    for numbers_str in series:
        # 使用正则表达式提取所有两位数的数字
        numbers_list = re.findall(r'\d{2}', numbers_str)

        for num in numbers_list:
            if num in all_numbers:
                counter[num] += 1

    # 确保所有可能的数字都有计数（即使为0）
    for num in all_numbers:
        if num not in counter:
            counter[num] = 0

    return counter


# 统计信息
stats = {}
for day in target_days:
    day_df = filtered_df[filtered_df["星期"] == day]

    # 提取前区号码（35个可能数字）
    front_counter = extract_numbers(day_df["前区号码"], 35)

    # 提取后区号码（12个可能数字）
    back_counter = extract_numbers(day_df["后区号码"], 12)

    stats[day] = {
        "前区频率": front_counter,
        "后区频率": back_counter,
        "总销售": day_df["总销售金额（元）"].sum(),
        "平均销售": day_df["总销售金额（元）"].mean(),
        "样本数": len(day_df),
        "销售明细": day_df["总销售金额（元）"].tolist()
    }

# 绘制前后区号码频率对比图
fig, axs = plt.subplots(3, 2, figsize=(18, 14), constrained_layout=True)
for idx, day in enumerate(target_days):
    # 前区频率
    front_counter = stats[day]["前区频率"]
    front_df = pd.DataFrame(front_counter.items(), columns=["号码", "出现次数"]).sort_values("号码")

    # 使用统一的颜色绘制前区
    sns.barplot(data=front_df, x="号码", y="出现次数", color="steelblue", ax=axs[idx, 0])
    axs[idx, 0].set_title(f"{day} 前区号码频率")
    axs[idx, 0].tick_params(axis='x', rotation=90)

    # 后区频率
    back_counter = stats[day]["后区频率"]
    back_df = pd.DataFrame(back_counter.items(), columns=["号码", "出现次数"]).sort_values("号码")

    # 使用统一的颜色绘制后区
    sns.barplot(data=back_df, x="号码", y="出现次数", color="darkorange", ax=axs[idx, 1])
    axs[idx, 1].set_title(f"{day} 后区号码频率")
    axs[idx, 1].tick_params(axis='x', rotation=90)

plt.show()

# 销售额对比图
sales_data = pd.DataFrame([{
    "开奖日": k,
    "平均销售": v["平均销售"],
    "总销售": v["总销售"],
    "期数": v["样本数"]
} for k, v in stats.items()])

fig, axs = plt.subplots(1, 2, figsize=(14, 5))

# 平均销售额
sns.barplot(data=sales_data, x="开奖日", y="平均销售", color="green", ax=axs[0])
axs[0].set_title("不同开奖日平均销售额")

# 总销售额
sns.barplot(data=sales_data, x="开奖日", y="总销售", color="purple", ax=axs[1])
axs[1].set_title("不同开奖日总销售额")

# 添加数值标签
for ax in axs:
    for p in ax.patches:
        ax.annotate(f"{p.get_height():,.0f}",
                    (p.get_x() + p.get_width() / 2., p.get_height()),
                    ha='center', va='center',
                    xytext=(0, 10),
                    textcoords='offset points')

plt.tight_layout()
plt.show()

# 显著性差异分析：单因素方差分析 ANOVA
sales_mon = stats["Monday"]["销售明细"]
sales_wed = stats["Wednesday"]["销售明细"]
sales_sat = stats["Saturday"]["销售明细"]

f_stat, p_val = f_oneway(sales_mon, sales_wed, sales_sat)
print("\n显著性分析 (销售额 ANOVA)")
print("F 值:", f_stat)
print("P 值:", p_val)
if p_val < 0.05:
    print("→ 不同开奖日的销售额存在显著差异")
else:
    print("→ 不同开奖日的销售额无显著差异")

# 输出分析结论
for day in target_days:
    # 获取前区频率最高的5个号码
    top_front = sorted(stats[day]["前区频率"].items(), key=lambda x: x[1], reverse=True)[:5]

    # 获取后区频率最高的3个号码
    top_back = sorted(stats[day]["后区频率"].items(), key=lambda x: x[1], reverse=True)[:3]

    print(f"【{day}】")
    print(f"前区高频号码 Top5: {', '.join([f'{num}({count}次)' for num, count in top_front])}")
    print(f"后区高频号码 Top3: {', '.join([f'{num}({count}次)' for num, count in top_back])}")
    print(
        f"平均销售额: {stats[day]['平均销售']:,.2f}元，总销售额: {stats[day]['总销售']:,.2f}元，期数: {stats[day]['样本数']}")
    print("-" * 60)